package com.managementSystem.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import com.managementSystem.client.RegisterBeanService;
import com.managementSystem.shared.RegisterBean;
import com.managementSystem.shared.StaffBean;

@SuppressWarnings("serial")
public class RegisterBeanServiceImpl extends RemoteServiceServlet implements RegisterBeanService {
	public void unapproveUser (String ID) {
		Connection con = null;
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET/servicedesk";
		String user = "hari666";
		String pass = "q3procn";

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,user,pass);
		} catch (Exception e) {
			// no exception
		}
		
		try {
			PreparedStatement ps = con.prepareStatement ("delete from Register where ID like '" + ID + "';");
			ps.executeUpdate();
			
			ps.close();
			
		} catch (Exception e) {

		}
	}

	@Override
	public void approveUser(String ID) {
		RegisterBean approve=getStaffApprovalBean(ID);
		
		Connection con = null;
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET/servicedesk";
		String user = "hari666";
		String pass = "q3procn";

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,user,pass);
		} catch (Exception e) {
			// no exception
		}
		
		try {
			System.out.println("INSERT into Staff " +"(ID, Firstname, Lastname,Username, Password,Email,PrimaryNumber,SecondaryNumber,Priority, Warnings,Type,DaysLate,DaysAbsent) "+"values ('"+approve.getId()+"', '"+approve.getFirstName()+"', '"+approve.getLastName()+"', '"+approve.getUserName()+"', '"+approve.getPassword()+"', '"+approve.getEmail()+"', '"+approve.getPrimaryNumber()+"', '"+approve.getSecondaryNumber()+"', '"+0+"', '"+0+"', '"+0+"', '"+0+"', '"+0+"')");
			PreparedStatement ps = con.prepareStatement ("INSERT into Staff " +"(ID, Firstname, Lastname,Username, Password,Email,PrimaryNumber,SecondaryNumber,Priority, Warnings,Type,DaysLate,DaysAbsent) "+"values ('"+approve.getId()+"', '"+approve.getFirstName()+"', '"+approve.getLastName()+"', '"+approve.getUserName()+"', '"+approve.getPassword()+"', '"+approve.getEmail()+"', '"+approve.getPrimaryNumber()+"', '"+0+"', '"+0+"', '"+0+"', '"+0+"', '"+0+"', '"+0+"')");
			System.out.println("AAAAA");
			ps.executeUpdate();
			System.out.println("BBBBB");
			ps.close();
			unapproveUser(ID);
			System.out.println("CCCCC");
		} catch (Exception e) {
			System.out.println("CANNOT APPROVE NEW STAFF");
		}
		
	}

	@Override
	public RegisterBean getStaffApprovalBean(String ID) {
		Connection con = null;
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET/servicedesk";
		String user = "hari666";
		String pass = "q3procn";

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,user,pass);
		} catch (Exception e) {
			// no exception
		}
		
		try {
			PreparedStatement ps = con.prepareStatement ("select * from Register where ID like binary '" + ID + "';");
			ResultSet result = ps.executeQuery();
			RegisterBean approve=null;
			while (result.next()) {
				approve= new RegisterBean();
				approve=createBean(result);
			}
			
			result.close();
			ps.close();
			return approve;
			
		} catch (Exception e) {

		}
		return null;
	}
	
	private static RegisterBean createBean(ResultSet result) throws SQLException {
		RegisterBean user=new RegisterBean();
		user.setId(result.getString("ID"));
		user.setFirstName(result.getString("Firstname"));
		user.setLastName(result.getString("Lastname"));
		user.setUserName(result.getString("Username"));
		user.setPassword(result.getString("Password"));
		user.setEmail(result.getString("Email"));
		user.setType(result.getInt("Type"));
		user.setPrimaryNumber(result.getString("PrimaryNumber"));
		user.setSecondaryNumber(result.getString("SecondaryNumber"));
		return user;
	}

	@Override
	public void addNewApproval(RegisterBean approvalBean) {
		Connection con = null;
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET/servicedesk";
		String user = "hari666";
		String pass = "q3procn";

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,user,pass);
		} catch (Exception e) {
			// no exception
		}
		
		try {
			PreparedStatement ps = con.prepareStatement ("INSERT into Register " +"(ID, Firstname, Lastname,Username, Password,Email, Type, PrimaryNumber,SecondaryNumber) "+"values ('"+approvalBean.getId()+"', '"+approvalBean.getFirstName()+"', '"+approvalBean.getLastName()+"', '"+approvalBean.getUserName()+"', '"+approvalBean.getPassword()+"', '"+approvalBean.getEmail()+"', '"+approvalBean.getType()+"', '"+approvalBean.getPrimaryNumber()+"', '"+approvalBean.getSecondaryNumber()+"')");
			ps.executeUpdate();
			ps.close();
			
		} catch (Exception e) {

		}
		
		
	}

	@Override
	public List<RegisterBean> getAllforApproval() {
		Connection con = null;
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET/servicedesk";
		String user = "hari666";
		String pass = "q3procn";

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,user,pass);
		} catch (Exception e) {
			// no exception
		}
		
		try {
			PreparedStatement ps = con.prepareStatement ("select * from Register;");
			ResultSet result = ps.executeQuery();
			List<RegisterBean> toApprove = new ArrayList<RegisterBean>();
			
			while (result.next()) {
				System.out.println("hgvhgvhgv");
				toApprove.add(createBean(result));
			}
			result.close();
			ps.close();
			
			return toApprove;
			
		} catch (Exception e) {

		}
		return null;
	}
	
}
